from flask import Flask, request, jsonify
from flask_sqlalchemy import SQLAlchemy
# from sqlalchemy import text
from flask_migrate import Migrate

app = Flask(__name__)
# 在app.config中作为连接数据库的配置
HOSTNAME = "linux.server"
PORT = 3306
DATABASE = "db_flask"
USERNAME = "root"
PASSWORD = "Chenhao$123456"
# 连接MYSQL5
# app.config["SQLALCHEMY_DATABASE_URI"] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?characterEncoding=utf-8&useSSL=false&serverTimezone=UTC"
# 连接MYSQL8
# SQLAlchemy似乎连接MYSQL5和MYSQL8没什么不同
app.config["SQLALCHEMY_DATABASE_URI"] = f"mysql+pymysql://{USERNAME}:{PASSWORD}@{HOSTNAME}:{PORT}/{DATABASE}?charset=utf8mb4"

# 通过SQLAlchemy创建db对象
# SQLAlchemy会读取app中的数据库连接信息
db = SQLAlchemy(app)
# 测试是否连接成功
# with app.app_context():
#     with db.engine.connect() as conn:
#         rs = conn.execute(text("select 1"))
#         print(rs.fetchone())        # (1,)

migrate = Migrate(app, db)

class User(db.Model):
    __tablename__ = "tb_user"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    # varchar
    username = db.Column(db.String(100), nullable=False)
    password = db.Column(db.String(100), nullable=False)
    email = db.Column(db.String(100), nullable=True)

    articles = db.relationship("Article", back_populates="author")


class Article(db.Model):
    __tablename__ = "tb_article"
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    title = db.Column(db.String(200), nullable=False)
    content = db.Column(db.Text, nullable=False)
    # 外键（类型必须与tb_user中的id一致）
    author_id = db.Column(db.Integer, db.ForeignKey("tb_user.id"))
    # 关联关系
    # 自动为User创建一个articles
    author = db.relationship("User", back_populates="articles")


# 把所有的表都同步到数据库中
# 如果表已经存在了会怎样？
# with app.app_context():
#     db.create_all()

@app.route("/")
def index():
    return "Hello World"

@app.route("/user/add/<username>/<password>")
def user_add(username, password):
    # 1、创建ORM对象
    user = User(username=username, password=password)
    # 2、将ORM对象存放到db.session中
    db.session.add(user)
    # 3、提交到数据库中
    db.session.commit()
    return "添加用户成功"

@app.route("/user/get_user_by_id/", methods=["POST"])
def get_user_by_id():
    data = request.json
    id = data.get("id")
    user = User.query.get(id)
    print("user.username", user.username)
    print("user.articles", user.articles)
    return jsonify({
        "code": 200,
        "message": "查询成功",
        # "data": user
    })
    
@app.route("/user/get_user_by_username/<username>")
def get_user_by_username(username):
    # user_list是Query对象，可循环，可切片，有点类似于数组
    user_list = User.query.filter_by(username=username)
    print(type(user_list))      # <class "flask_sqlalchemy.query.Query">
    for user in user_list:
        print(user.username, user.password)
    return "查询数据成功"


@app.route("/user/update")
def user_update():
    user = User.query.get(1)
    user.password = "654321"
    db.session.commit()
    return "更新用户成功"


@app.route("/user/delete")
def user_delete():
    user = User.query.get(1)
    db.session.delete(user)
    db.session.commit()
    return "删除用户成功"


if __name__ == "__main__":
    app.run(debug=True, port=9000, host="0.0.0.0")


